--SELECT * FROM FN_LOAD_WIP_FLUSH_BACKLOGS() where MFG_Part_number = '3DM-00TQ-T001'
Alter FUNCTION FN_LOAD_WIP_FLUSH_BACKLOGS()
RETURNS @FN_RESULTS TABLE ( BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT, MFG_PART_NUMBER VARCHAR(50), MFG_PROCESSSTEP_ID INT, MFG_PRODUCT_ID INT, BLANK_FGI_PART_NUMBER VARCHAR(50), BLANK_FGI_PROCESSSTEP_ID INT, BLANK_FGI_PRODUCT_ID INT, BACKLOG_QUANTITY INT, SHIPPED_QTY INT, PARTIAL_SHIP_BACKLOG_QTY INT )
AS
BEGIN
	DECLARE @RESULTS TABLE ( BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT, MFG_PART_NUMBER VARCHAR(50), MFG_PROCESSSTEP_ID INT, MFG_PRODUCT_ID INT, BLANK_FGI_PART_NUMBER VARCHAR(50), BLANK_FGI_PROCESSSTEP_ID INT, BLANK_FGI_PRODUCT_ID INT, BACKLOG_QUANTITY INT, SHIPPED_QTY INT, PARTIAL_SHIP_BACKLOG_QTY INT )
		
	INSERT INTO @RESULTS 
	SELECT  ( 
		SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID =  
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
		) AS BASE_PRODUCT,
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END,
		P.PART_NUMBER, P.PROCESSSTEP_ID, P.PRODUCT_ID, 
		'', 0, 0,
		ISNULL ( SUM( ISNULL( COOL.COOL_Qty, 0 ) ), 0 )  AS BACKLOG_QTY,
		( SELECT ISNULL( SUM( ISNULL(CSLIT_QTY_SHIPPED , 0 ) ), 0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED') AS SHIPPED_QUANTITY, 
		NULL AS PARTIAL_SHIP_BACKLOG_QTY
	FROM Customer_Open_Order_LineItem COOL, Sales_Order SO, PRODUCT P
	WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND COOL.COOL_Status <> 'SHIPPED' AND ( ( SO.SO_Is_Sample = 1 ) OR ( SO.SO_Is_Sample = 2 ) ) --AND COOL.Product_ID = ?
	GROUP BY P.PART_NUMBER, P.PROCESSSTEP_ID, P.PRODUCT_ID, P.P_PARENT_PRODUCT_ID, COOL.LINEITEM_ID 
	
	--INSERT @FN_RESULTS SELECT * FROM @RESULTS

	INSERT INTO @FN_RESULTS 
	SELECT BASE_PRODUCT, BASE_PRODUCT_ID, MFG_PART_NUMBER, MFG_PROCESSSTEP_ID, MFG_PRODUCT_ID, BLANK_FGI_PART_NUMBER, BLANK_FGI_PROCESSSTEP_ID, 
	BLANK_FGI_PRODUCT_ID, SUM( BACKLOG_QUANTITY ), SUM( SHIPPED_QTY ), SUM ( BACKLOG_QUANTITY - SHIPPED_QTY ) AS PARTIAL_SHIP_BACKLOG_QTY FROM @RESULTS
	GROUP BY BASE_PRODUCT, BASE_PRODUCT_ID, MFG_PART_NUMBER, MFG_PROCESSSTEP_ID, MFG_PRODUCT_ID, BLANK_FGI_PART_NUMBER, BLANK_FGI_PROCESSSTEP_ID, BLANK_FGI_PRODUCT_ID

 	RETURN
END


